/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.ArrayList;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import pojo.Foreignkey;
import pojo.Property;
import pojo.Table;

/**
 *
 * @author HoangLiem
 */
public class ExecForeignkeyXML {
    /*
     * Add 1 FK vào table
     * 
     */

    public static boolean addFkToTable(Document doc, String tableName, Foreignkey fk) {
        boolean result = true;
        if (!checkedForeignkey(doc, tableName, fk)) {
            return result = false;
        }
        try {
            doc.getDocumentElement().normalize();
            NodeList nl = doc.getElementsByTagName("table");
            Node node = null;
            Element table = null;
            for (int i = 0; i < nl.getLength(); i++) {
                node = nl.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    table = (Element) node;
                    if (table.getAttribute("name").equalsIgnoreCase(tableName)) {
                        Element foreignkey = doc.createElement("foreignkey");
                        foreignkey.setAttribute("propertyref", fk.getPropertyref().toLowerCase());
                        foreignkey.setAttribute("tableref", fk.getTableref().toLowerCase());
                        foreignkey.setAttribute("idref", fk.getIdref().toLowerCase());

                        Node rowNode = ExecXML.getFirstNodeByNodeName(doc, tableName, "row");
                        if (rowNode != null) {
                            table.insertBefore(foreignkey, rowNode);
                            table.insertBefore(doc.createTextNode("\n\t\t"), rowNode);
                        } else {
                            table.appendChild(doc.createTextNode("\t"));
                            table.appendChild(foreignkey);
                            table.appendChild(doc.createTextNode("\n\t"));
                        }
                    }
                }
            }
            ExecXML.saveXmlFile(doc, ExecTableXML.getNameDatabase(doc) + ".xml");
        } catch (Exception e) {
            result = false;
            e.printStackTrace();
        }
        return result;
    }

    public static boolean addFkToTable(String databaseName, String tableName, Foreignkey fk) {
        try {
            databaseName = databaseName.trim();
            tableName = tableName.trim();
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            return addFkToTable(doc, tableName, fk);
        } catch (Exception ex) {
            return false;
        }
    }
    /*
     * Hàm này lấy foreignkey của 1 table
     */

    public static ArrayList<Foreignkey> getForeignkeyOfTable(Document doc, String tableName) {
        ArrayList<Foreignkey> foreignKeys = new ArrayList<Foreignkey>();
        try {
            doc.getDocumentElement().normalize();
            NodeList nl = doc.getElementsByTagName("foreignkey");
            Node node = null;
            Element pt = null;
            for (int i = 0; i < nl.getLength(); i++) {
                node = nl.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    pt = (Element) node;
                    Foreignkey temp = new Foreignkey();
                    Element pr = (Element) pt.getParentNode();
                    if (pr.getAttribute("name").equalsIgnoreCase(tableName)) {
                        temp.setPropertyref(pt.getAttribute("propertyref").toString());
                        temp.setTableref(pt.getAttribute("tableref").toString());
                        temp.setIdref(pt.getAttribute("idref").toString());
                        foreignKeys.add(temp);
                    }
                }
            }
            if (foreignKeys.isEmpty()) {
                foreignKeys = null;
            }
        } catch (Exception e) {
            foreignKeys = null;
            e.printStackTrace();
        }
        return foreignKeys;
    }

    /*
     * Ham nay kiem tra hop le cua Foreignkey, true : hop le, false: khong hop le
     */
    public static boolean checkedForeignkey(Document doc, String tableName, Foreignkey fk) {
        return (checkedExistTableRef(doc, fk) && checkedPropertyref(doc, tableName, fk) && checkedIdref(doc, tableName, fk) && checkedType(doc, tableName, fk) && !checkedSameFk(doc, tableName, fk));
    }

    /*
     * Hàm kiem tra tableref co ton tai không, nếu tồn tại thì true
     */
    public static boolean checkedExistTableRef(Document doc, Foreignkey fk) {
        boolean result = false;
        try {
            ArrayList<String> tableNames = ExecTableXML.getNamesOfTable(doc);
            result = ExecXML.isExistedString(tableNames, fk.getTableref());
        } catch (Exception ex) {
            result = true;
        }
        return result;
    }

    public static boolean checkedExistTableRef(String databaseName, Foreignkey fk) {
        boolean result = false;
        try {
            Document doc = ExecXML.getDocument(databaseName + ".xml");
            ArrayList<String> tableNames = ExecTableXML.getNamesOfTable(doc);
            result = ExecXML.isExistedString(tableNames, fk.getTableref());
        } catch (Exception ex) {
            result = true;
        }
        return result;
    }
    /*
     * Hàm này kiểm tra propertyref xem co tôn tai trong table hien thoi hay khong
     */

    public static boolean checkedPropertyref(Document doc, String tableName, Foreignkey fk) {
        boolean result = false;
        int flag = 0;
        try {
            Table table = ExecTableXML.getTableFromDatabase(doc, tableName);
            if (table != null) {
                ArrayList<Property> ids = table.getIdList();
                ArrayList<Property> properties = table.getPropertyList();
                ArrayList<String> propertyrefNames = new ArrayList<String>();
                if (ids != null && !ids.isEmpty()) {
                    for (int i = 0; i < ids.size(); i++) {
                        propertyrefNames.add(ids.get(i).getName());
                    }
                }
                if (properties != null && !properties.isEmpty()) {
                    for (int i = 0; i < properties.size(); i++) {
                        propertyrefNames.add(properties.get(i).getName());
                    }
                }
                String fkPropertyrefName = fk.getPropertyref();
                String[] temp = null;
                temp = fkPropertyrefName.split(",");
                if (temp.length > 0) {
                    for (int i = 0; i < temp.length; i++) {
                        if (!ExecXML.isExistedString(propertyrefNames, temp[i])) {
                            flag = -1;
                        }
                    }
                }
                if (flag == 0) {
                    result = true;
                } else {
                    result = false;
                }
            } else {
                result = false;
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    public static boolean checkedPropertyref(String databaseName, String tableName, Foreignkey fk) {
        databaseName = databaseName.trim();
        tableName = tableName.trim();
        Document doc = ExecXML.getDocument(databaseName + ".xml");
        return checkedPropertyref(doc, tableName, fk);
    }
    /*
     * Hàm này kiểm tra idref có tồn tai trong bang tham chieu
     */

    public static boolean checkedIdref(Document doc, String tableName, Foreignkey fk) {
        boolean result = false;
        int flag = 0;
        try {
            Table tableRef = ExecTableXML.getTableFromDatabase(doc, fk.getTableref());
            if (tableRef != null) {
                ArrayList<Property> idsTableref = tableRef.getIdList();
                ArrayList<String> idNamesTableref = new ArrayList<String>();
                if (idsTableref != null && !idsTableref.isEmpty()) {
                    for (int i = 0; i < idsTableref.size(); i++) {
                        idNamesTableref.add(idsTableref.get(i).getName());
                    }
                }
                String idref = fk.getIdref();
                String[] temp = null;
                temp = idref.split(",");
                if (temp.length > 0) {
                    for (int i = 0; i < temp.length; i++) {
                        if (!ExecXML.isExistedString(idNamesTableref, temp[i])) {
                            flag = -1;
                        }
                    }
                }
                if (flag == 0) {
                    result = true;
                } else {
                    result = false;
                }
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    /*
     * Kiểm tra loại dữ liệu proref, idref trung nhau không
     */
    public static boolean checkedType(Document doc, String tableName, Foreignkey fk) {
        boolean result = false;

        try {
            ArrayList<Property> p1 = new ArrayList<Property>();
            ArrayList<Property> p2 = new ArrayList<Property>();

            String fkPropertyrefName = fk.getPropertyref();
            String[] temp = null;
            temp = fkPropertyrefName.split(",");

            String idrefName = fk.getIdref();
            String[] temp1 = null;
            temp1 = idrefName.split(",");

            int kt = 0;
            for (int i = 0; i < temp.length; i++) {
                p1.add(ExecPropertyXML.getPropertyOfTable(doc, tableName, temp[i]));
                p1.add(ExecIdXML.getIdOfTable(doc, tableName, temp[i]));
            }
            for (int i = 0; i < temp1.length; i++) {
                p2.add(ExecPropertyXML.getPropertyOfTable(doc, fk.getTableref(), temp1[i]));
                p2.add(ExecIdXML.getIdOfTable(doc, fk.getTableref(), temp1[i]));
            }
            for (int i = 0; i < p1.size(); i++) {
                String st = p1.get(i).getName();
                if (st.equalsIgnoreCase("null")) {
                    p1.remove(i);
                }
            }
            for (int i = 0; i < p2.size(); i++) {
                String st = p2.get(i).getName();
                if (st.equalsIgnoreCase("null")) {
                    p2.remove(i);
                }
            }

            for (int i = 0; i < p1.size(); i++) {
                for (int j = 0; j < p2.size(); j++) {
                    String type1 = p1.get(i).getType().toString();
                    String type2 = p2.get(j).getType().toString();
                    if (type1.equalsIgnoreCase(type2)) {
                        String length1 = Integer.toString(p1.get(i).getLength());
                        String length2 = Integer.toString(p2.get(j).getLength());
                        if (length1.equalsIgnoreCase(length2)) {
                            kt++;
                        }
                    }
                }
            }
            if (kt == temp.length) {
                result = true;
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    public static boolean checkedType(String databaseName, String tableName, Foreignkey fk) {
        databaseName = databaseName.trim();
        tableName = tableName.trim();
        Document doc = ExecXML.getDocument(databaseName + ".xml");
        return checkedType(doc, tableName, fk);
    }
    /*
     * Hàm này kiểm tra có trùng foreignkey không (vẫn còn viết linh động hơn nếu split chuỗi, không cần xét thứ tự   
     */

    public static boolean checkedSameFk(Document doc, String tableName, Foreignkey fk) {
        boolean result = false;
        try {
            ArrayList<Foreignkey> fkList = getForeignkeyOfTable(doc, tableName);
            ArrayList<String> tbref = new ArrayList<String>();
            ArrayList<String> idref = new ArrayList<String>();
            ArrayList<String> ptref = new ArrayList<String>();
            if (fkList != null && !fkList.isEmpty()) {
                for (int i = 0; i < fkList.size(); i++) {
                    tbref.add(fkList.get(i).getTableref());
                    idref.add(fkList.get(i).getIdref());
                    ptref.add(fkList.get(i).getPropertyref());
                    if (tbref.get(i).equalsIgnoreCase(fk.getTableref()) && idref.get(i).equalsIgnoreCase(fk.getIdref()) && ptref.get(i).equalsIgnoreCase(fk.getPropertyref())) {
                        result = true;
                        break;
                    }
                }
            }
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }

    public static boolean checkedExistedFk(String databaseName, String tableName, Foreignkey fk) {
        databaseName = databaseName.trim();
        tableName = tableName.trim();
        Document doc = ExecXML.getDocument(databaseName + ".xml");
        return checkedSameFk(doc, tableName, fk);
    }
    /*
     * Hàm này xóa 1 khóa ngoại
     */

    public static boolean deleteForeignkey(Document doc, String tableName, Foreignkey fk) {
        boolean result = true;
        if (!checkedSameFk(doc, tableName, fk)) {
            return result = false;
        }
        try {

            doc.getDocumentElement().normalize();
            NodeList nl = doc.getElementsByTagName("foreignkey");
            Node node = null;
            Element pt = null;
            for (int i = 0; i < nl.getLength(); i++) {
                node = nl.item(i);
                if (node.getNodeType() == Node.ELEMENT_NODE) {
                    pt = (Element) node;
                    Foreignkey temp = new Foreignkey();
                    Element pr = (Element) pt.getParentNode();
                    if (pr.getAttribute("name").equalsIgnoreCase(tableName)) {
                        //Lay pt = fk
                        if (pt.getAttribute("propertyref").equalsIgnoreCase(fk.getPropertyref().toString())
                                && pt.getAttribute("tableref").equalsIgnoreCase(fk.getTableref().toString())
                                && pt.getAttribute("idref").equalsIgnoreCase(fk.getIdref().toString())) {
                            pr.removeChild(node);
                        }
                    }
                }
            }
            ExecXML.saveXmlFile(doc, ExecTableXML.getNameDatabase(doc) + ".xml");
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }
    /*
     * Hàm này lấy 1 foreignkey của 1 table cho trước
     */

    public static Foreignkey get1Foreignkey(Document doc, String tableName, String tableref, String idref, String propertyref) {
        Foreignkey kq = new Foreignkey();
        try {
            String xPath = "database/table[translate(@name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','abcdefghijklmnopqrstuvwxyz')='" + tableName.toLowerCase() + "']/foreignkey[@idref='" + idref.toLowerCase() + "' and @tableref='" + tableref.toLowerCase() + "' and @propertyref='" + propertyref.toLowerCase() + "']";
            Node tb = (Node) ExecXML.execXpath(doc, xPath, "node");
            Element fk = (Element) tb;
            kq.setIdref(fk.getAttribute("idref").toLowerCase());
            kq.setPropertyref(fk.getAttribute("propertyref").toLowerCase());
            kq.setTableref(fk.getAttribute("tableref").toLowerCase());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return kq;
    }

    /*
     * Hàm này update 1 foreignkey
     */
    public static boolean updateForeignkey(Document doc, Foreignkey fk1, Foreignkey fk2) {
        boolean result = true;
        try {
            fk1.setIdref(fk2.getIdref());
            fk1.setPropertyref(fk2.getPropertyref());
            fk1.setTableref(fk2.getTableref());
            ExecXML.saveXmlFile(doc, ExecTableXML.getNameDatabase(doc) + ".xml");
        } catch (Exception ex) {
            result = false;
        }
        return result;
    }
}
